

ALTER               proc sp_create_update_ws_lot_cavium (
  @part_number varchar(50), 
  @lot_number varchar(50), 
  @qty_in int,
  @qty_out int,
  @date_in datetime,
  @date_out datetime,
  @inventory_type varchar(50),
  @vendor_name varchar(50),
  @date_code varchar(50),
  @vendor_lot varchar(50),
  @lot_comments varchar(1000))
  as
 
  begin
  	declare @process_step_id int
  	declare @location_id int
  	declare @int int
  	declare @normal_cost float
  	declare @lot_id int
  	declare @this_lot_status varchar(50)
  
 	-- Commented by KumaraSwamy on 20th August, 2004
        /*if ( ( @vendor_lot is null ) OR ( @vendor_lot = '' ) )
 	begin
 		set @vendor_lot = @lot_number 
         end*/
  	select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where product.part_number = @part_number and PS_Type_Name like @inventory_type and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
  	print @process_step_id
  	select @location_id = Location_ID from Location where L_Type like @inventory_type and L_Vendor_Name = @vendor_name
  	print @location_id
  	select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
  	print @lot_id
  	if @lot_id is null -- Check if the lot exists
  	begin -- Create Lot
  		select @normal_cost = PSLT_Cost_Normal from Process_Step_Location_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id
  		insert into Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Lot_Cost, Lot_Current_Status, Timestamp, Lot_Date_Code, Lot_Comments) values (@process_step_id, @location_id, @lot_number, (@normal_cost * @qty_in), 'Running', CURRENT_TIMESTAMP, @date_code, @lot_comments)
  		select @lot_id = @@IDENTITY
  		print @lot_id
  	end
  
  	else
  	begin
  		update lot_table set lot_current_status = 'Running', Lot_Date_Code = @date_code, Lot_Comments = @lot_comments where lot_id = @lot_id
  	end
  
  	declare @inv_lot_id int
  	select @inv_lot_id = Lot_ID from Inventory_Lot where lot_id = @lot_id
  	if @inv_lot_id is null
  	begin
  		declare @temp_qty_out int
  		if @qty_out is not null 
  		begin
  			set @temp_qty_out = @qty_out
  		end
  		else
  		begin
  			set @temp_qty_out = 0
  		end
 
  		--Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, timestamp, IL_Vendor_Lot_Number) values (@lot_id, @qty_in - @temp_qty_out, null, @date_in, @date_out, CURRENT_TIMESTAMP, @vendor_lot)
  		print @qty_out
  	
  		if @qty_in = @qty_out 
  		begin
			Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, timestamp, IL_Vendor_Lot_Number) values (@lot_id, @qty_in - @temp_qty_out, null, @date_in, @date_out, CURRENT_TIMESTAMP, @vendor_lot)
  			update Lot_Table set Lot_Current_Status = 'Close', Lot_Comments = @lot_comments where Lot_ID = @lot_id
  			insert into Inventory_Lot_History (Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, timestamp, IL_Current_Status, IL_Vendor_Lot_Number) 
  			values (@lot_id, null, @date_in, @date_out, @qty_in, @qty_out, null, null, CURRENT_TIMESTAMP, 'Close', @vendor_lot)
  		end
  		else
  		begin
 			if @qty_out is not null and @qty_out > 0
 			begin
				Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, timestamp, IL_Vendor_Lot_Number) values (@lot_id, @qty_in - @temp_qty_out, null, @date_in, null, CURRENT_TIMESTAMP, @vendor_lot)
  				insert into Inventory_Lot_History (Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, timestamp, IL_Current_Status, IL_Vendor_Lot_Number) 
  				values (@lot_id, null, @date_in, @date_out, @qty_in, @qty_out, null, null, CURRENT_TIMESTAMP, 'Running', @vendor_lot)
                         end
			 else
			 begin
				Insert into Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Out, IL_Date_In, IL_Date_Out, TIMESTAMP, IL_Vendor_Lot_Number) values (@lot_id, @qty_in , null, @date_in, null, CURRENT_TIMESTAMP, @vendor_lot)
			 end
  			update Lot_Table set Lot_Current_Status = 'Running', Lot_Comments = @lot_comments where Lot_ID = @lot_id
  		end
  	end
  	
  	else
  	begin
  		if @date_out is not null-- update the lot where the qty_out and date_out vals are given
  		begin
  			declare @max_timestamp datetime
   			--update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in - @qty_out, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
   			if ( @qty_in - @qty_out ) = 0
   			begin	
				update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in - @qty_out, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
  				select @max_timestamp = MAX(Timestamp) from Inventory_Lot_History where Lot_ID = @inv_lot_id
   				update lot_table set lot_current_status = 'Close', Lot_Comments = @lot_comments where lot_id = @lot_id
   				UPDATE Inventory_Lot_History SET IL_Packing_Method = null, IL_Date_In = @date_in, IL_Date_Out = @date_out, IL_Qty_In = @qty_in, IL_Qty_Out = @qty_out, IL_Qty_Shipped = null, IL_Qty_Balance = null, timestamp = CURRENT_TIMESTAMP, IL_Current_Status = 'Close', IL_Vendor_Lot_Number = @vendor_lot where (Lot_ID = @inv_lot_id ) AND ( Timestamp = @max_timestamp)
				--insert into Inventory_Lot_History (Lot_ID, IL_Packing_Method, IL_Date_In, IL_Date_Out, IL_Qty_In, IL_Qty_Out, IL_Qty_Shipped, IL_Qty_Balance, timestamp, IL_Current_Status, IL_Vendor_Lot_Number) 
   				--values (@lot_id, null, @date_in, @date_out, @qty_in, @qty_out, null, null, CURRENT_TIMESTAMP, 'Close', @vendor_lot)
   			end
  			else
  			begin
  				update Inventory_Lot set IL_Date_In = @date_in, IL_Date_Out = null, IL_Qty_In = @qty_in - @qty_out, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
  				select @max_timestamp = MAX(Timestamp) from Inventory_Lot_History where Lot_ID = @inv_lot_id and IL_Qty_In = @qty_in
  				update Inventory_Lot_History set IL_Qty_Out = @qty_out, IL_Date_Out = @date_out where (Lot_ID = @inv_lot_id ) AND ( Timestamp = @max_timestamp)
  			end
  		--select @max_timestamp = MAX(Timestamp) from Assembly_Lot_History where Lot_ID = @lot_id and AL_Qty_In = @qty_in
  		end
  		else
  		begin
  			update lot_table set lot_current_status = 'Running', Lot_Comments = @lot_comments where lot_id = @lot_id
  			update Inventory_Lot set IL_Date_In = @date_in, IL_Qty_In = @qty_in, IL_Date_Out = null, IL_Qty_Out = null, timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot where Lot_ID = @inv_lot_id
                         
  		end
  	end
  
  end



